package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

import java.util.ArrayList;

import model.SubContractorJob;

public class DBSubContractorJob implements IFSubContractorJob
{
	private Connection con;

	public DBSubContractorJob()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createSubContractorJob(SubContractorJob job) throws SQLException
	{
		int rc = -1;

		String query = "SELECT outsider_job_id FROM outsider_job WHERE outsider_job_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("createSubContractorJob", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, job.getJobName());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{	 		
				query = "INSERT INTO outsider_job(outsider_job_name) VALUES(?)";

				if (DBConnection.doDebug())
					DBConnection.printDebug("createSubContractorJob", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, job.getJobName());

					rc = ps.executeUpdate();
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - createSubContractorJob - INSERT: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createSubContractorJob - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updateSubContractorJob(SubContractorJob job) throws SQLException
	{
		int rc = -1;

		String query = "SELECT outsider_job_id FROM outsider_job WHERE outsider_job_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("updateSubContractorJob", query);

		try 
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, job.getJobName());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "UPDATE outsider_job SET outsider_job_name = ? WHERE outsider_job_id = ?";

				if (DBConnection.doDebug())
					DBConnection.printDebug("updateSubContractorJob", query);

				try 
				{
					ps = con.prepareStatement(query);

					ps.setString(1, job.getJobName());
					ps.setInt(2, job.getJobID());

					rc = ps.executeUpdate();
				}
				catch (Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - updateSubContractorJob - UPDATE: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - updateSubContractorJob - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public SubContractorJob findSubContractorJobByName(String jobName) throws SQLException
	{
		ResultSet results;
		SubContractorJob newSubContractorJob = new SubContractorJob();

		String query = "SELECT * FROM outsider_job WHERE outsider_job_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("printAllSubContractorJobs", query);

		try 
		{	
			PreparedStatement ps = con.prepareStatement(query);
			ps.setString(1, jobName);

			results = ps.executeQuery();

			while(results.next())
			{
				newSubContractorJob = buildSubContractorJobObject(results);
			}

			ps.close();     
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - findSubContractorJobByName - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newSubContractorJob;
	}

	@Override
	public int findSubcontractorJobCount(int jobID) throws SQLException
	{
		int result = -1;

		String query = "SELECT COUNT(outsider_id) AS job_count FROM outsider WHERE outsider_job_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("findSubcontractorJobCount", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, jobID);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				result = rs.getInt("job_count");
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - findSubcontractorJobCount - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return result;
	}

	@Override
	public ArrayList<SubContractorJob> printAllSubContractorJobs() throws SQLException
	{
		ArrayList<SubContractorJob> SubContractorJobList = new ArrayList<SubContractorJob>();

		String query = "SELECT * FROM outsider_job ORDER BY outsider_job_name ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("printAllSubContractorJobs", query);

		try 
		{	
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);

			ResultSet results = stmt.executeQuery(query);

			while(results.next())
			{
				SubContractorJob newSubContractorJob = buildSubContractorJobObject(results);

				SubContractorJobList.add(newSubContractorJob);
			}

			stmt.close();     
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - printAllSubContractorJobs - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return SubContractorJobList;
	}

	private SubContractorJob buildSubContractorJobObject(ResultSet results) throws SQLException
	{
		SubContractorJob newSubContractorJob = new SubContractorJob();

		newSubContractorJob.setJobID(results.getInt("outsider_job_id"));
		newSubContractorJob.setJobName(results.getString("outsider_job_name"));

		return newSubContractorJob;
	}
}